package com.zhang.common.file;


import com.zhang.common.util.X509TrustUtil;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.net.ssl.*;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;

/**
 * ExcelReader
 * 使用方式(例)：
 * ExcelReader er = new ExcelReader(filepath);
 *          // 读取第1个工作簿
 *             er.setSheetIndex(0);
 *             int row = er.getRowCount();
 *             int columnCountrow = er.getColumnCount();
 *             for (int i = 0; i < row - 1; i++) {
 *                  // readCell(int column, int row)
 *                 String data = er.readCell(1, i + 1);
 *                 if (null != data && !"".equals(data)) {
 *                     for (int j = 1; j < columnCountrow - 1; j++) {
 *                         data += ";" + er.readCell(j + 1, i + 1);
 *                     }
 *                     returnDataList.add(data);
 *                 }
 *             }
 *
 *             // 关闭Excel文件
 *             er.close();
 * @author zhangxin
 * @date 2021/6/17
 */
public class ExcelReader {
    private static final Logger logger = LoggerFactory.getLogger(ExcelReader.class);
    private Workbook workbook = null;
    private Sheet sheet = null;
    private int rowNo = 0;
    private int columnNo = 0;
    private int rowCount = 0;
    private int columnCount = 0;

    public ExcelReader(String excelFile) throws Exception {
        if (!excelFile.toLowerCase().startsWith("http://") && !excelFile.toLowerCase().startsWith("https://")) {
            this.workbook = Workbook.getWorkbook(new File(excelFile));
        } else {
            URL url = new URL(excelFile);
            InputStream is = null;
//            InputStream is = url.openStream();
            if (excelFile.startsWith("https://")) {
                is = getUrlHttps(url);
            } else {
                is = getUrlHttp(url);
            }
            this.workbook = Workbook.getWorkbook(is);
        }

    }


    public static InputStream getUrlHttp(URL url) throws IOException {
        InputStream is = url.openStream();
        return is;
    }

    public static InputStream getUrlHttps(URL url) throws Exception {

        SSLContext sslcontext = SSLContext.getInstance("SSL", "SunJSSE");
        sslcontext.init(null, new TrustManager[]{new X509TrustUtil()}, new java.security.SecureRandom());
        HostnameVerifier ignoreHostnameVerifier = new HostnameVerifier() {
            @Override
            public boolean verify(String s, SSLSession sslsession) {
                logger.warn("WARNING: Hostname is not matched for cert.");
                return true;
            }
        };
        HttpsURLConnection.setDefaultHostnameVerifier(ignoreHostnameVerifier);
        HttpsURLConnection.setDefaultSSLSocketFactory(sslcontext.getSocketFactory());
        HttpsURLConnection urlCon = (HttpsURLConnection) url.openConnection();
        urlCon.setConnectTimeout(6000);
        urlCon.setReadTimeout(6000);
        int code = urlCon.getResponseCode();
        if (code != HttpURLConnection.HTTP_OK) {
            throw new Exception("文件读取失败");
        }
        // 读文件流
//        DataInputStream in = new DataInputStream(urlCon.getInputStream());
        return urlCon.getInputStream();
    }

    public ExcelReader(InputStream is) throws BiffException, IOException {
        this.workbook = Workbook.getWorkbook(is);
    }

    public void setSheetIndex(int nIndex) {
        this.sheet = this.workbook.getSheet(nIndex);
        this.rowNo = 0;
        this.columnNo = 0;
        this.rowCount = this.sheet.getRows();
        this.columnCount = this.sheet.getColumns();
    }

    public boolean nextRow() {
        ++this.rowNo;
        this.columnNo = 0;
        return this.rowNo < this.rowCount;
    }

    public void toRow(int nRow) {
        this.rowNo = nRow;
        this.columnNo = 0;
    }

    public void toColumn(int nColumn) {
        this.columnNo = nColumn;
    }

    public String readCell() {
        if (this.sheet == null) {
            return null;
        } else {
            return this.rowNo < this.rowCount && this.columnNo < this.columnCount ? this.sheet.getCell(this.columnNo++, this.rowNo).getContents() : null;
        }
    }

    public String readCell(int column, int row) {
        if (this.sheet == null) {
            return null;
        } else {
            return row < this.rowCount && column < this.columnCount ? this.sheet.getCell(column, row).getContents() : null;
        }
    }

    public int getRowCount() {
        return this.sheet == null ? -1 : this.rowCount;
    }

    public int getColumnCount() {
        return this.sheet == null ? -1 : this.columnCount;
    }

    public void close() {
        this.workbook.close();
    }
}
